package com.itheima.repository;

import com.itheima.entity.Animal;
import org.apache.ibatis.annotations.Param;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface AnimalRepository extends JpaRepository<Animal, Long> {



    // 查询最大的 id
    @Query(value = "SELECT COALESCE(MAX(id), 0) FROM animals", nativeQuery = true)
    Long findMaxId();

    // 使用 JPQL 查询状态为 APPROVED 的动物
    @Query(value = "SELECT a.* FROM animals a JOIN reviews r ON a.id = r.animal_id WHERE r.review_status = :status", nativeQuery = true)
    List<Animal> findAnimalsByReviewStatusNative(@Param("status") String status);

    // 使用自定义查询方法，支持根据动物特征进行搜索
    @Query("SELECT a FROM Animal a WHERE " +
            "(a.species = :species OR :species IS NULL) AND " +
            "(a.size = :size OR :size IS NULL) AND " +
            "(a.color = :color OR :color IS NULL) AND " +
            "(a.location = :location OR :location IS NULL)")
    List<Animal> searchByCriteria(String species, String size, String color, String location);

    // 使用自定义查询方法，根据审核状态过滤动物
    @Query(value = "SELECT a.* FROM animals a JOIN reviews r ON a.id = r.animal_id WHERE r.review_status = :status " +
            "AND (a.species = :species OR :species IS NULL) " +
            "AND (a.size = :size OR :size IS NULL) " +
            "AND (a.color = :color OR :color IS NULL) " +
            "AND (a.location = :location OR :location IS NULL)", nativeQuery = true)
    List<Animal> searchCriteriaByReviewStatusNative(String species, String size, String color, String location, String status);
}

